﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using GeneralLibs;

namespace Lists
{
    public class NhatKyThuChi
    {
        public static DataTable NhatKyThuTien(DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return NhatKyThuTien(cmd, ngayBatDau, ngayKetThuc, doiTuongCondition);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static DataTable NhatKyThuTien(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                                       "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                                       "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                                       "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                                       "       AND {0}  \n", doiTuongCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DataTable NhatKyThuTien(DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return NhatKyThuTien(cmd, ngayBatDau, ngayKetThuc);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static DataTable NhatKyThuTien(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                                       "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                                       "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                                       "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongThu(DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongThu(cmd, ngayBatDau, ngayKetThuc, doiTuongCondition);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongThu(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhNo \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                                       "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                                       "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                                       "       AND {0}  \n", doiTuongCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhNo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhNo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongThu(DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongThu(cmd, ngayBatDau, ngayKetThuc);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongThu(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhNo \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                                       "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                                       "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhNo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhNo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongThuBanHang(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            //Tài khoản có là đích danh 131 - phải thu công nợ
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhNo \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu = '131' OR C.SoHieu = '5111')\n" +
                                       "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                                       "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhNo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhNo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongThuTheoTaiKhoanDoiUng(DateTime ngayBatDau, DateTime ngayKetThuc, DanhMucTaiKhoan dmTKDoiTung)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongThuTheoTaiKhoanDoiUng(cmd, ngayBatDau, ngayKetThuc, dmTKDoiTung);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongThuTheoTaiKhoanDoiUng(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, DanhMucTaiKhoan dmTKDoiTung)
        {
            try
            {
                string tkDoiUngCondition = "(";
                foreach (TaiKhoan tk in dmTKDoiTung)
                {
                    tkDoiUngCondition += string.Format("C.SoHieu LIKE '{0}%' OR ", tk.SoHieu);
                }
                if (tkDoiUngCondition.EndsWith(" OR "))
                    tkDoiUngCondition = tkDoiUngCondition.Substring(0, tkDoiUngCondition.Length - 4) + ")";

                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhNo \n" +
                                       "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                                       "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND {0}\n" +
                                       "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n",
                                       tkDoiUngCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhNo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhNo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DataTable NhatKyChiTien(DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return NhatKyThuTien(cmd, ngayBatDau, ngayKetThuc, doiTuongCondition);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static DataTable NhatKyChiTien(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                                      "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                                      "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu NOT LIKE'111%' AND B.SoHieu NOT LIKE'112%') \n" +
                                      "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                      "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID \n" +
                                      "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID \n" +
                                      "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n" +
                                      "       AND {0}  \n", doiTuongCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DataTable NhatKyChiTien(DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return NhatKyThuTien(cmd, ngayBatDau, ngayKetThuc);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static DataTable NhatKyChiTien(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                                    "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                                    "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu NOT LIKE'111%' AND B.SoHieu NOT LIKE'112%') \n" +
                                    "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                    "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID \n" +
                                    "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID \n" +
                                    "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongChi(DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongThu(cmd, ngayBatDau, ngayKetThuc, doiTuongCondition);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongChi(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, string doiTuongCondition)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhCo \n" +
                                     "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu NOT LIKE'111%' AND B.SoHieu NOT LIKE'112%') \n" +
                                     "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                     "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID \n" +
                                     "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID \n" +
                                     "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n" +
                                     "       AND {0}  \n", doiTuongCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhCo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhCo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongChi(DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongThu(cmd, ngayBatDau, ngayKetThuc);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongChi(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhCo \n" +
                                     "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu NOT LIKE'111%' AND B.SoHieu NOT LIKE'112%') \n" +
                                     "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                     "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID \n" +
                                     "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID \n" +
                                     "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhCo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhCo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongChiMuaHang(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc)
        {
            // Tài khoản Ghi Nợ đích danh là 331 - phải trả
            try
            {
                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhCo \n" +
                                     "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu = '331' OR B.SoHieu = '1561') \n" +
                                     "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                     "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID \n" +
                                     "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID \n" +
                                     "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n");
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhCo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhCo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static decimal TongChiTheoTaiKhoanDoiUng(DateTime ngayBatDau, DateTime ngayKetThuc, DanhMucTaiKhoan dmTKDoiTung)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return TongChiTheoTaiKhoanDoiUng(cmd, ngayBatDau, ngayKetThuc, dmTKDoiTung);
            }
            catch (Exception ex)
            {
                throw new CustomException("Lỗi khi thống kê Nhật ký thu tiền");
            }
            finally
            {
                con.Close();
                con = null;
            }
        }
        public static decimal TongChiTheoTaiKhoanDoiUng(SqlCommand cmd, DateTime ngayBatDau, DateTime ngayKetThuc, DanhMucTaiKhoan dmTKDoiTung)
        {
            try
            {
                string tkDoiUngCondition = "(";
                foreach (TaiKhoan tk in dmTKDoiTung)
                {
                    tkDoiUngCondition += string.Format("B.SoHieu LIKE '{0}%' OR ", tk.SoHieu);
                }
                if (tkDoiUngCondition.EndsWith(" OR "))
                    tkDoiUngCondition = tkDoiUngCondition.Substring(0, tkDoiUngCondition.Length - 4) + ")";

                cmd.CommandText = string.Format("SELECT SUM(A.GiaTri) PhatSinhCo \n" +
                                      "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND {0} \n" +
                                      "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID  AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%')\n" +
                                      "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc \n",
                                      tkDoiUngCondition);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("NgayKetThuc", ngayKetThuc);

                SqlDataReader dr = cmd.ExecuteReader();
                decimal phatSinh = 0;
                if (dr.Read())
                {
                    if (dr["PhatSinhCo"] is decimal)
                        phatSinh = (decimal)dr["PhatSinhCo"];
                }
                else phatSinh = 0;
                dr.Close();
                return phatSinh;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
    }
}
